package model;


import java.sql.*;
import java.util.*;


import javax.naming.NamingException;
//import javax.sql.rowset.CachedRowSet;

//import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;

import forms.ApplicantFormInsert;


public class StudentModel {
	private static String insertStr = "INSERT INTO Students (lastname, firstname, middle, citizenship,residence,residency,street,city,state,zipcode,areacode,number,telephonecode,specialization,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	
	public static void insertStudent(ApplicantFormInsert student) throws DbException{
		try {
			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			conn.setAutoCommit(false);
			
			//System.out.println(student.getSpecialization()+"\n\n\n");
			
			pstmt = conn.prepareStatement("SELECT id FROM Specialization WHERE name=?");
			pstmt.setString(1,student.getSpecialization());
			rs = pstmt.executeQuery();
			int spec_id = -1;
			if (rs.next()){
				spec_id = rs.getInt(1);
			}
			else 
				System.out.println("spec error!");
        
			pstmt = conn.prepareStatement("SELECT id FROM Countries WHERE name=?");
			pstmt.setString(1,student.getCitizenship());
			rs = pstmt.executeQuery();
			int citizenship_id = -1;
			if(rs.next()){
				citizenship_id = rs.getInt(1);
			}
			else
				System.out.println("citi error!");
        
			pstmt = conn.prepareStatement("SELECT id FROM Countries WHERE name=?");
			pstmt.setString(1,student.getResidence());
			rs = pstmt.executeQuery();
			int residence_id = -1;
			if(rs.next()){
				residence_id = rs.getInt(1);
			}
			else
				System.out.println("resi error!");
        
			
			pstmt = conn.prepareStatement(insertStr);
			
			pstmt.setString(1, student.getLastname());
            pstmt.setString(2, student.getFirstname());
            pstmt.setString(3, student.getMiddle());
           
            pstmt.setInt(4, citizenship_id);
            pstmt.setInt(5, residence_id);
           
            pstmt.setString(6, student.getResidency());
            pstmt.setString(7, student.getStreet());
            pstmt.setString(8, student.getCity());
            pstmt.setString(9, student.getState());
            
            pstmt.setInt(10, Integer.parseInt(student.getZipcode()));
            pstmt.setInt(11, Integer.parseInt(student.getAreacode()));
            pstmt.setInt(12, Integer.parseInt(student.getNumber()));
            pstmt.setInt(13, Integer.parseInt(student.getTelephonecode()));
            
            pstmt.setInt(14, spec_id);
            pstmt.setString(15, "Pending");
			

            pstmt.executeUpdate();
	
            conn.commit();

	
	
            
            pstmt.close();
            conn.setAutoCommit(true);
            conn.close();
            
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
}






/*
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.StudentFormDelete;
import forms.StudentFormInsert;
import forms.StudentFormUpdate;

public class StudentModel {

	private static String selectStr = "SELECT id, first_name, middle_name, last_name FROM students ORDER BY id";
	private static String insertStr = "INSERT INTO students (first_name, middle_name, last_name) VALUES (?, ?, ?)";
	private static String updateStr = "UPDATE students SET first_name = ?, "
			+ "middle_name = ?, last_name = ? WHERE id = ?";
	private static String deleteStr = "DELETE FROM students WHERE id = ?";

	public static CachedRowSet getAllStudents() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectStr);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allStudents = pStmt.executeQuery();

			CachedRowSet crsStudents = new CachedRowSetImpl();
			crsStudents.populate(allStudents);

			allStudents.close();
			pStmt.close();
			conn.close();

			return crsStudents;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void insertStudent(StudentFormInsert student)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// INSERT student values INTO the Students table.
			PreparedStatement pStmt = conn.prepareStatement(insertStr);

			pStmt.setString(1, student.getFirst());
			pStmt.setString(2, student.getMiddle());
			pStmt.setString(3, student.getLast());
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void updateStudent(StudentFormUpdate student)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// UPDATE student values in the Students table.
			PreparedStatement pStmt = conn.prepareStatement(updateStr);

			pStmt.setString(1, student.getFirst());
			pStmt.setString(2, student.getMiddle());
			pStmt.setString(3, student.getLast());
			pStmt.setInt(4, Integer.parseInt(student.getId()));
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void deleteStudent(StudentFormDelete student)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// DELETE students FROM the Students table.
			PreparedStatement pStmt = conn.prepareStatement(deleteStr);

			pStmt.setInt(1, student.getId());
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

}
*/
